package com.ks.app.admin.util;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.ks.app.admin.hibernate.KsMerchantRegist;
import com.ks.app.admin.service.impl.BaseServiceImpl;

public class ExportExcel extends BaseServiceImpl{
/**
* 导出Excel表
* @param rs
* 数据库结果集
* @param filePath
* 要保存的路径，文件名为 fileName.xls
* @param sheetName
* 工作簿名称 工作簿名称，本方法目前只支持导出一个Excel工作簿
* @param columnName
* 列名，类型为Vector<STRING></STRING>
*/
public static void write2Excel(List<KsMerchantRegist> rs, String fileName,HttpServletResponse response) throws SQLException, IOException,RowsExceededException, WriteException {
	// 获得导出数据的列数
	String[] title = { "序号", "审核状态", "商家全程", "商家地址", "商家电话", "商家编号", };
	
	
	OutputStream os = response.getOutputStream();
	response.reset();
	//定义头信息
	response.setContentType("application/msexcel");
	String sheetTitle  =  URLEncoder.encode("merchant.xls","UTF-8");
	response.setHeader("Content-disposition", "attachment;filename="+sheetTitle);
	WritableWorkbook wwb = Workbook.createWorkbook(os);
	
	
//	OutputStream os = response.getOutputStream();// 取得输出流   
//    response.reset();// 清空输出流   
//
//    response.setHeader("Content-disposition", "attachment; filename="+new String("Book1".getBytes("GB2312"),"8859_1")+".xls");// 设定输出文件头   
//    response.setContentType("application/msexcel");// 定义输出类型 
//    
//    WritableWorkbook wwb = Workbook.createWorkbook(os); // 建立excel文件   
//	
	
	WritableSheet ws = wwb.createSheet(fileName, 0);
	ws.setColumnView(0, 20); // 设置列的宽度
	ws.setColumnView(1, 20); // 设置列的宽度
	ws.setColumnView(2, 20); // 设置列的宽度
	ws.setColumnView(3, 50); // 设置列的宽度
	ws.setColumnView(4, 20); // 设置列的宽度
	ws.setColumnView(5, 20); // 设置列的宽度
	 //    设置单元格的文字格式
    WritableFont wf = new WritableFont(WritableFont.ARIAL,13,WritableFont.BOLD,false,
            UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
    WritableCellFormat wcf = new WritableCellFormat(wf);
    wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
    wcf.setAlignment(Alignment.CENTRE); 
    
    WritableFont wf1 = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
            UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
    WritableCellFormat wcf1 = new WritableCellFormat(wf1);
    wcf1.setVerticalAlignment(VerticalAlignment.CENTRE); 
    wcf1.setAlignment(Alignment.CENTRE);
    
    ws.setRowView(0, 500);

	

		try {
		// 写入列明
			for (int i = 0; i < title.length; i++) {
			Label labelName = new Label(i, 0, title[i],wcf);
			ws.addCell(labelName);
			}
		// 写入数据
			for (int i = 0; i < rs.size(); i++) {
				KsMerchantRegist tp = (KsMerchantRegist) rs.get(i);
			Label label0 = new Label(0, i + 1, String.valueOf(i + 1),wcf1);
			String state = "";
			if (!"".equals(tp.getKsMerStatus())) {
			state = "待审核";
			} else {
			state = "已审核";
			}
			Label label1 = new Label(1, i + 1, state,wcf1);
			Label label2 = new Label(2, i + 1, tp.getKsFullName(),wcf1);
			Label label3 = new Label(3, i + 1, tp.getKsMerAddress(),wcf1);
			Label label4 = new Label(4, i + 1, tp.getKsMerPhone(),wcf1);
			Label label5 = new Label(5, i + 1, tp.getKsMerNo(),wcf1);
			ws.addCell(label0);
			ws.addCell(label1);
			ws.addCell(label2);
			ws.addCell(label3);
			ws.addCell(label4);
			ws.addCell(label5);
			}
			System.out.println("完成输出" + wwb);
			} catch (Exception ex) {
		ex.printStackTrace();
			} finally {
		// 关闭Excel工作薄对象
		System.out.println("关闭流");
		wwb.write();
		if(wwb!=null){
			wwb.close();
		}
		if(os!=null){
			os.flush();
			os.close();
		}
		}
}
}
